﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using SocialIn.Core.Data;
using System.Data;
using System.Web;
using System.Web.Security;

namespace SocialIn.Biz
{
    public sealed class Intro
    {
        ConnectionStringSettings _BaseDBCon = null;

        public Intro()
        {
            _BaseDBCon = ConfigurationManager.ConnectionStrings[ConfigName.DBConfig];
        }

        /// <summary>
        /// 메인에 나오는 신규 도와주세요!!!! 정보를 가져옵니다.
        /// </summary>
        /// <returns></returns>
        public DataTable GetTopHelpNotice()
        {
            string query = @" SELECT TOP 5 
	                            A.Seq, A.NgoID, A.Title, A.TalentID, A.Detail, A.WishStartDate, A.WishEndDate
	                            , A.ReceiptStartDate, A.ReceiptEndDate
	                            , A.Place, A.MapX, A.MapY, A.MapPanoramaID, A.MapPan
	                            , A.MapTilt, A.MapZoom, A.Complement, A.PersonInCharge, A.Contact, A.WriteDate 
	                            , B.SUBCATEGORY, B.CODENAME, B.Etc1, B.Etc2, B.Etc3
	                            , C.NgoName 
                            FROM 
	                            HelpRequest AS A 
                            LEFT JOIN 
	                            CodeMaster AS B 
                            ON A.TalentID = B.SUBCATEGORY 
                            LEFT JOIN 
	                            NGO AS C 
                            ON A.NgoID = C.NgoID
                            WHERE A.Canceled = 0 AND B.CATEGORY = 'M003' 
                            ORDER BY A.WriteDate DESC ";

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<DataTable> result = sql.ExecuteDataTable(query);

            return (DataTable)result.ReturnValue;
        }


        /// <summary>
        /// 메인에 나오는 신규 기부자 정보를 가져옵니다.
        /// </summary>
        /// <returns></returns>
        public DataTable GetNewVolunteer()
        {
            string query = @" SELECT TOP 5 
	                            A.UserID, A.UserName, A.Password, A.Email, A.SelfIntroduction, A.PicturePath, A.JoinVerified, A.Seceded
	                            , C.TalentID, C.CATEGORY, C.SUBCATEGORY, C.CODENAME 
                            FROM 
	                            Member AS A 
                            LEFT JOIN 
	                            MembersInRoles AS B 
                            ON A.UserID = B.UserID 
                            LEFT JOIN 
	                            (
		                            SELECT A.UserID, A.TalentID 
		                            , B.CATEGORY, B.SUBCATEGORY, B.CODENAME  
		                            FROM 
			                            MyTalent AS A 
		                            LEFT JOIN 
			                            CodeMaster AS B 
		                            ON A.TalentID = B.SUBCATEGORY
		                            WHERE  A.Deleted = 0 AND A.Preferred = 1 
		                            AND B.CATEGORY = 'M003' 
	                            ) AS C 
                            ON A.UserID = C.UserID
                            WHERE 
	                            B.RoleID = 'R003' 
                            AND 
	                            A.Seceded = 0 
                            AND C.TalentID IS NOT NULL
                            ORDER BY 
	                            A.WriteDate DESC ";

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<DataTable> result = sql.ExecuteDataTable(query);

            return (DataTable)result.ReturnValue;
        }

        /// <summary>
        /// 메인에 나오는 감사했어요! 정보를 가져옵니다.
        /// </summary>
        /// <returns></returns>
        public DataTable GetTopAfterNote()
        {
            string query = @"SELECT TOP 5 
	                            A.Seq, A.HelpRequestID, A.UserID, A.Comment, A.WriteDate, A.Deleted
	                            , B.Seq, B.NgoID, B.Title, B.TalentID, B.Detail, B.WishStartDate, B.WishEndDate
	                            , B.Place, B.PersonInCharge, B.Contact
	                            , C.PersonInChargeID, C.NgoName
	                            , D.CODENAME, D.Etc1, D.Etc2, D.Etc3
                            FROM 
	                            AfterNote AS A 
                            LEFT JOIN 
	                            HelpRequest AS B 
                            ON A.HelpRequestID = B.Seq 
                            INNER JOIN 
	                            NGO AS C 
                            ON B.NgoID = C.NgoID 
                            LEFT JOIN 
                            (
	                            SELECT SUBCATEGORY, CODENAME, Etc1, Etc2, Etc3
	                            FROM CodeMaster 
	                            WHERE CATEGORY = 'M003'
                            ) AS D 
                            ON B.TalentID = D.SUBCATEGORY
                            WHERE 
	                            A.Deleted = 0 AND B.Completed = 1 
                            ORDER BY 
	                            A.WriteDate DESC ";
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<DataTable> result = sql.ExecuteDataTable(query);

            return (DataTable)result.ReturnValue;
        }

        //public void LogIn(string userID, string plainPassword, out Result<bool> resultValue)
        //{
        //    resultValue = new Result<bool>();

        //    string hashPwd = Convert.ToBase64String(Encoding.UTF8.GetBytes(plainPassword));

        //    string query = "";
        //    Dictionary<string, string> parameters = new Dictionary<string, string>();


        //    SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
        //    Result<DataTable> dtResult = sql.ExecuteDataTable(query, parameters);

        //    if (dtResult.ReturnValue == null || dtResult.ReturnValue.Rows.Count == 0)
        //    {
        //        resultValue.Completed = false;
        //        resultValue.Message = "아이디와 비밀번호를 확인하세요.";
        //        resultValue.Error = dtResult.Error;
        //    }
        //    else
        //    {
        //        Member currentUser = new Member();

        //        HttpContext.Current.Session[ConfigName.SessionMember] = currentUser;
        //        FormsAuthentication.RedirectFromLoginPage(userID, true);
        //    }
        //}


    }
}
